import datetime

import akshare as ak

from python_quant.common import constant
from python_quant.common.sql.mysql.SqlHelper import SqlHelper


def add_to_stocks_pool(symbols_to_add):
    stock_zh_a_spot_em_df = ak.stock_zh_a_spot_em()
    for symbol in symbols_to_add:
        symbol_list = stock_zh_a_spot_em_df['代码'].values
        if symbol in symbol_list:
            stock_em = stock_zh_a_spot_em_df[(stock_zh_a_spot_em_df['代码'] == symbol)]
            if stock_em.empty:
                continue
            add_or_update_stock_pool(symbol, float(stock_em['最新价']))


def add_to_stock_pool(symbol_to_add, last_price):
    result = select_last_add_stock_pool(symbol_to_add)
    if result is None:
        sql = 'insert into stock_pool (symbol, add_price, add_date, valid, base_price) values (%s, %s, %s, %s, %s)'
        args = (symbol_to_add, last_price, datetime.datetime.now().strftime(constant.DATE_FORMAT_PARTTERN_Y_M_D_H_M_S),
                constant.BOOL.TRUE, last_price)
        SqlHelper.insert(sql, args)


def add_or_update_stock_pool(symbol_to_add, last_price):
    result = select_last_add_stock_pool(symbol_to_add)
    if result is None:
        sql = 'insert into stock_pool (symbol, add_price, add_date, valid, base_price) values (%s, %s, %s, %s, %s)'
        args = (symbol_to_add, last_price, datetime.datetime.now().strftime(constant.DATE_FORMAT_PARTTERN_Y_M_D_H_M_S),
                constant.BOOL.TRUE, last_price)
        SqlHelper.insert(sql, args)
    elif (result['valid'] == constant.BOOL.TRUE) or (
            result['valid'] == constant.BOOL.FALSE and result['had_position'] == constant.BOOL.TRUE):
        sql = 'update stock_pool set add_price=%s, add_date=%s,update_date=%s, valid=%s, base_price=%s  where id=%s'
        args = (
            last_price, datetime.datetime.now().strftime(constant.DATE_FORMAT_PARTTERN_Y_M_D_H_M_S),
            datetime.datetime.now().strftime(constant.DATE_FORMAT_PARTTERN_Y_M_D_H_M_S),
            constant.BOOL.TRUE, last_price, result['id'])
        SqlHelper.update(sql, args)
    elif result['valid'] == constant.BOOL.FALSE and result['had_position'] == constant.BOOL.FALSE:
        del_stock_pool_trade_info(result['id'])
        sql = 'insert into stock_pool (symbol, add_price, add_date, valid, base_price) values (%s, %s, %s, %s, %s)'
        args = (symbol_to_add, last_price, datetime.datetime.now().strftime(constant.DATE_FORMAT_PARTTERN_Y_M_D_H_M_S),
                constant.BOOL.TRUE, last_price)
        SqlHelper.insert(sql, args)


def select_stock_pool(symbol_to_add):
    sql = 'select * from stock_pool where symbol=%s and valid =%s'
    args = (symbol_to_add, constant.BOOL.TRUE)
    result = SqlHelper.fetch_all(sql, args)
    return result


def select_last_add_stock_pool(symbol_to_add):
    sql = 'select * from stock_pool where symbol=%s order by  add_date desc limit 1'
    args = (symbol_to_add,)
    result = SqlHelper.fetch_one(sql, args)
    return result


def select_last_add_valid_stock_pool(symbol_to_add):
    sql = 'select * from stock_pool where symbol=%s and valid =%s order by  add_date desc limit 1'
    args = (symbol_to_add, constant.BOOL.TRUE)
    result = SqlHelper.fetch_one(sql, args)
    return result


def select_last_add_all_stock_pool(symbol_to_add):
    sql = 'select * from stock_pool where symbol=%s  order by  add_date desc limit 1'
    args = (symbol_to_add,)
    result = SqlHelper.fetch_one(sql, args)
    return result


def select_all_valid_stock_pool():
    sql = 'select * from stock_pool where valid =%s'
    args = (constant.BOOL.TRUE,)
    result = SqlHelper.fetch_all(sql, args)
    return result


def select_all_stock_pool():
    sql = 'select * from stock_pool'
    result = SqlHelper.fetch_all(sql, args=None)
    return result


def update_stock_pool(up_max_amount_price, up_max_amount, up_rate, down_max_amount_price, down_max_amount, down_rate,
                      base_price, id):
    sql = 'update stock_pool set up_max_amount_price=%s, up_max_amount=%s, up_rate=%s, down_max_amount_price=%s, ' \
          'down_max_amount=%s, down_rate=%s, base_price=%s, update_date=%s  where id=%s'
    update_date = datetime.date.today()
    args = (up_max_amount_price, up_max_amount, up_rate, down_max_amount_price, down_max_amount, down_rate, base_price,
            update_date, id)
    effect_row = SqlHelper.update(sql, args)
    return effect_row


def update_stock_pool_had_position(had_position, id):
    sql = 'update stock_pool set had_position=%s , update_date=%s where id=%s'
    update_date = datetime.date.today()
    args = (had_position, update_date, id)
    effect_row = SqlHelper.update(sql, args)
    return effect_row


def update_stock_pool_tp_fpnl_sl_fpnl(tp_fpnl_amount, sl_fpnl_amount, id):
    sql = 'update stock_pool set  tp_fpnl=%s,sl_fpnl=%s, update_date=%s  where id=%s'
    update_date = datetime.date.today()
    args = (tp_fpnl_amount, sl_fpnl_amount, update_date, id)
    effect_row = SqlHelper.update(sql, args)
    return effect_row


def update_stock_pool_trade_info(keep_loss_times, total_loss_amount, tp_fpnl, sl_fpnl, id, last_exec_id,
                                 exec_buy_price):
    sql = 'update stock_pool set  keep_loss_times=%s,total_loss_amount=%s,tp_fpnl=%s,sl_fpnl=%s,last_exec_id=%s,' \
          'exec_buy_price=%s, update_date=%s  ' \
          'where id=%s'
    update_date = datetime.date.today()
    args = (keep_loss_times, total_loss_amount, tp_fpnl, sl_fpnl, last_exec_id, exec_buy_price, update_date, id)
    effect_row = SqlHelper.update(sql, args)
    return effect_row


def del_stock_pool_trade_info(id):
    sql = 'delete from stock_pool where id=%s'
    args = (id,)
    SqlHelper.delete(sql, args)
